#! /usr/bin/env python
#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import time, sys
sys.path.insert(0, '../apps')


import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()



def run():
    
    settings = Ini('../apps/settings.ini')

    #准开发需求相关报表数据处理
    req = db.metadata.tables['tasks_develop']                                                        
    #tx_table = db.metadata.tables['requirments_tx_count']                      

    #获取本月月初和月末时间
    month_first_day,month_last_day,quarter_view,quarter = get_quarter()

    #判断本月末是否是季度末
    quarter = long(quarter)
    if(quarter <> 3) and (quarter <> 6) and (quarter <> 9) and (quarter <> 12):
        sys.exit()

    #计算本月各版本所处状态，算法为：目前状态为未完成或在本月内状态改为完成的版本
    new = select([req.c.stage,func.count(req.c.task_id)],
                  or_(req.c.stage != 'FN',
                    and_(req.c.stage == 'FN',
                    req.c.stage_enterTime >= month_first_day,
                    req.c.stage_enterTime <= month_last_day)
                     )
                ).group_by(req.c.stage)

    #连接表version_month_report_stage
    req_model = get_model('version_quarter_report_stage')
    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    req_model.remove(req_model.c.month_date == month_last_day)

    #执行select查询语句
    new_rs = new.execute()
    #old_rs = old.execute()

    #定义数据字典data
    data = {}

    #初始化data
    task_stage = dict(settings.PARA.TASK_STAGE)
    for stages in task_stage:
    #    print 'stages',stages
        tx = stages
    #    print 'tx',tx
        y = 'version_num_%s' % tx
        data[y] = 0
    data['month_date'] = month_last_day
    data['quarter'] = quarter_view
    data['total_num'] = 0

    #给data赋值
    for row in new_rs:
        stage, count = row
        y = 'version_num_%s' % stage
        data[y] = count
        data['total_num'] += count

    #将data中数据存入数据库
    #print data
    r = req_model(**data)
    r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise
